﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DBHelperModel;
using MyEntity;

namespace MyDAL
{
    public class StudentInfoDAl
    {
        //新增专业
        public int Add(StudentInfoEntity pro)
        {
            string sql = "insert into StudentInfo(StuID,StuName,StuAge,StuSex,StuHobby,ProfessionID) values(@StuID,@StuName,@StuAge,@StuSex,@StuHobby,@ProfessionID)";
            DBHepler.PrepareCmd(sql);
            DBHepler.SetParamter("StuID", pro.StuID);
            DBHepler.SetParamter("StuName", pro.StuName);
            DBHepler.SetParamter("StuAge", pro.StuAge);
            DBHepler.SetParamter("StuSex", pro.StuSex);
            DBHepler.SetParamter("StuHobby", pro.StuHobby);
            DBHepler.SetParamter("ProfessionID", pro.ProfessionID);
            return DBHepler.ExecNonQuery();
        }


        //删除
        public int Delete(string StuId)
        {
            string sql = "delete from StudentInfo where StuID =@StuID";
            DBHepler.PrepareCmd(sql);
            DBHepler.SetParamter("StuID", StuId);
            return DBHepler.ExecNonQuery();
        }

        //修改
        public int Update(StudentInfoEntity pro)
        {
            string sql = "update StudentInfo set StuName=@StuName,StuAge=@StuAge,StuSex=@StuSex,StuHobby=@StuHobby,ProfessionID=@ProfessionID where StuID=@StuID";
            DBHepler.PrepareCmd(sql);
            DBHepler.SetParamter("StuID", pro.StuID);
            DBHepler.SetParamter("StuName", pro.StuName);
            DBHepler.SetParamter("StuAge", pro.StuAge);
            DBHepler.SetParamter("StuSex", pro.StuSex);
            DBHepler.SetParamter("StuHobby", pro.StuHobby);
            DBHepler.SetParamter("ProfessionID", pro.ProfessionID);
            return DBHepler.ExecNonQuery();
        }
        //列表

        public List<StudentInfoEntity> List()
        {
            DataTable dt = new DataTable();
            string sql = "select * from StudentInfo";
            DBHepler.PrepareCmd(sql);
            dt = DBHepler.ExecQuery();
            List<StudentInfoEntity> list = new List<StudentInfoEntity>();
            foreach (DataRow item in dt.Rows)
            {
                StudentInfoEntity entity = new StudentInfoEntity();
                entity.StuID = item["StuID"].ToString();
                entity.StuName = item["StuName"].ToString();
                entity.StuAge = Convert.ToInt32(item["StuAge"].ToString());
                entity.StuSex = item["StuSex"].ToString();
                entity.StuHobby = item["StuHobby"].ToString();
                entity.ProfessionID = Convert.ToInt32(item["ProfessionID"].ToString());
                list.Add(entity);
            }
            return list;
        }


        //详情
        public StudentInfoEntity Detail(int ID)
        {
            DataTable dt = new DataTable();
            string sql = "select * from StudentInfo where StuID =@StuID";
            DBHepler.PrepareCmd(sql);
            DBHepler.SetParamter("StuID", ID);
            dt = DBHepler.ExecQuery();
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            StudentInfoEntity entity = new StudentInfoEntity();
            entity.StuID = dt.Rows[0]["StuID"].ToString();
            entity.StuName = dt.Rows[0]["StuName"].ToString();
            entity.StuAge = Convert.ToInt32(dt.Rows[0]["StuAge"].ToString());
            entity.StuSex = dt.Rows[0]["StuSex"].ToString();
            entity.StuHobby = dt.Rows[0]["StuHobby"].ToString();
            entity.ProfessionID = Convert.ToInt32(dt.Rows[0]["ProfessionID"].ToString());
            return entity;
        }

        //根据指定条件获取列表
        public List<StudentInfoEntity> Search(StudentInfoEntity studentInfo)//动态扩展
        {
            DataTable dt = new DataTable();
            //动态扩展sql
            string sql = "select * from StudentInfo inner join ProfessionInfo on StudentInfo.ProfessionID = ProfessionInfo.ProfessionID where 1 = 1";
            //Console.WriteLine(s);
            //    if (studentInfo.StuID.ToString().Equals("0"))
            //{
            //    sql += " and StuID = @StuID";
            //    DBHepler.SetParamter("StuID", studentInfo.StuID);
            //}
            if (studentInfo.ProfessionID != 0)
            {
                sql += " and StudentInfo.ProfessionID = @ProfessionID" ;
                //DBHepler.SetParamter("ProfessionID", studentInfo.ProfessionID);
            }
            //校验姓名是否为空
            if (!studentInfo.StuName.Equals(""))
            {
                sql += " AND StuName LIKE @StuName";
                //DBHepler.SetParamter("StuName", "%"+studentInfo.StuName+"%");
            }
            DBHepler.PrepareCmd(sql);

            /*
             * 此时不能设置完sql立马调用 DBHelper中的SsetParamter方法设置参数
             * 因为还没有准备sql
             */

            DBHepler.SetParamter("ProfessionID", studentInfo.ProfessionID);
            DBHepler.SetParamter("StuName", "%" + studentInfo.StuName + "%");
            dt = DBHepler.ExecQuery();
            List<StudentInfoEntity> list = new List<StudentInfoEntity>();
            foreach (DataRow item in dt.Rows)
            {
                StudentInfoEntity entity = new StudentInfoEntity();
                entity.StuID = item["StuID"].ToString();
                entity.StuName = item["StuName"].ToString();
                entity.StuAge = Convert.ToInt32(item["StuAge"].ToString());
                entity.StuSex = item["StuSex"].ToString();
                entity.StuHobby = item["StuHobby"].ToString();
                entity.ProfessionID = Convert.ToInt32(item["ProfessionID"].ToString());
                entity.ProfessionName = item["ProfessionName"].ToString();
                list.Add(entity);
            }
            return list;
        }
    }
}

